--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('ART_COMPMESURE');

 DELETE FROM se_ART_COMPMESURE;



 INSERT INTO se_ART_COMPMESURE
 (SELECT Decode(p.x,0,'TREF',1,'LREF',2,'CVOL',3,'CVOLQ',NULL) mesure_cod,
         Nvl((SELECT comp_num FROM se_art_gene g WHERE g.gene_cod || g.gene_ind =m.id),0) comp_num,
         'UNR' typunite_cod,
         0 enreg_id,
         999 compmesure_rang,
         'N' enreg_s,
         Decode(p.x,3,'O','N') formule_ok,
         Decode(p.x,0,libre3_tairef,1,libre4_lonref,2,clasvolume,3,null,NULL) compmesure_val,
         NULL combinaison_rang
    FROM uv_pproduits m, &outils.pivot p,uv_pproduits_statut_actuel s
    WHERE p.x<4 AND m.codgestion in ('2','1') AND m.prodbase=m.ID
      AND s.id=m.id AND (s.statut<900 OR s.statut IS NULL)
      AND Decode(p.x,0,libre3_tairef,1,libre4_lonref,2,clasvolume,3,clasvolume,NULL) IS NOT null
     );

 UPDATE se_ART_COMPMESURE SET COMPMESURE_VAL = 'STD' WHERE MESURE_COD IN ('LREF','TREF') AND COMPMESURE_VAL ='....' ;
 UPDATE se_ART_COMPMESURE SET COMPMESURE_VAL = Trim(REPLACE(COMPMESURE_VAL,'.',' ')) WHERE  MESURE_COD IN ('LREF','TREF');
 UPDATE se_ART_COMPMESURE SET COMPMESURE_VAL = REPLACE(COMPMESURE_VAL,' ','.')  WHERE MESURE_COD IN ('LREF','TREF');

 DROP TABLE se_ART_COMPMESURE_rech_tailpat;
 CREATE TABLE se_ART_COMPMESURE_rech_tailpat AS (
 SELECT r.*,longpat_cod,taillepat_cod,
        ( SELECT taillemarq_cod || '->' ||taillepat_cod
          from se_met_mensurlt llt
          WHERE llt.gene_cod= r.gene_cod AND llt.gene_ind=r.gene_ind
            AND llt.longpat_cod=lref
            AND llt.taillemarq_cod=tref) rech_by_taimarq,
        ( SELECT taillepat_cod
          from se_met_mensurlt llt
          WHERE llt.gene_cod= r.gene_cod AND llt.gene_ind=r.gene_ind
            AND llt.longpat_cod=lref
            AND llt.taillemarq_cod=tref) taillepat_cod_trouve
 FROM
    se_met_mensurlt lt,
    (SELECT a.comp_num,a.gene_cod,a.gene_ind,ml.COMPMESURE_VAL lref,mt.COMPMESURE_VAL tref
    FROM se_ART_COMPMESURE mt , se_ART_COMPMESURE ml,se_art_comp a
    WHERE mt.MESURE_COD= 'TREF'
      AND ml.MESURE_COD= 'LREF'
      AND mt.COMP_NUM(+)=ml.comp_num
      AND a.comp_num=ml.comp_num) r
  WHERE lt.gene_cod(+)= r.gene_cod AND lt.gene_ind(+)=r.gene_ind
    AND lt.longpat_cod(+)=lref
    AND lt.taillepat_cod(+)=tref
    AND lt.taillepat_cod IS null ) ;

 UPDATE se_ART_COMPMESURE m SET COMPMESURE_VAL = (SELECT  taillepat_cod_trouve
                                                FROM se_ART_COMPMESURE_rech_tailpat r
                                                WHERE r.comp_num=m.comp_num
                                                  AND r.taillepat_cod_trouve IS NOT null)
                            WHERE EXISTS (SELECT  taillepat_cod_trouve
                                          FROM se_ART_COMPMESURE_rech_tailpat r
                                          WHERE r.comp_num=m.comp_num
                                            AND r.taillepat_cod_trouve IS NOT null)
                              AND m.MESURE_COD='TREF'  ;



 INSERT INTO se_ART_COMPMESURE
 (SELECT Decode(p.x,0,'POIDN',1,'POIDB',NULL) mesure_cod,
         Nvl((SELECT comp_num FROM se_art_refe g WHERE g.refe_cod || g.refe_ind =m.id),0) comp_num,
         'UNR' typunite_cod,
         0 enreg_id,
         999 compmesure_rang,
         'N' enreg_s,
         'N' formule_ok,
         Decode(p.x,0,POIDSNET/1000,1,poids/1000,NULL) compmesure_val,
         NULL combinaison_rang
    FROM UV_CMODELES m, &outils.pivot p
    WHERE  Decode(p.x,0,POIDSNET/1000,1,poids/1000,NULL) IS NOT null AND Decode(p.x,0,POIDSNET/1000,1,poids/1000,NULL) !='0'
     );

 UPDATE se_ART_COMPMESURE SET COMPMESURE_VAL = 0||COMPMESURE_VAL WHERE MESURE_COD IN ('POIDN','POIDB') AND instr(COMPMESURE_VAL,'.') =1;



 UPDATE se_ART_COMPMESURE tf SET COMPMESURE_RANG=Nvl((select COMPMESURE_RANG FROM &erp.ART_COMPMESURE f WHERE f.comp_num=tf.comp_num AND f.mesure_cod=tf.mesure_cod  ),999) ;
 UPDATE se_ART_COMPMESURE c SET COMPMESURE_RANG =
  (SELECT Min(rang) FROM
      (SELECT COMP_NUM,MESURE_COD,COMPMESURE_RANG,
        Rank() OVER (PARTITION BY COMP_NUM order BY COMP_NUM,COMPMESURE_RANG,MESURE_COD) rang
      FROM se_ART_COMPMESURE) r
      WHERE r.comp_num=c.comp_num AND r.mesure_cod=c.mesure_cod
  ) ;

 UPDATE se_ART_COMPMESURE tf SET enreg_id=(select enreg_id FROM &erp.ART_COMPMESURE f WHERE f.comp_num=tf.comp_num AND f.mesure_cod=tf.mesure_cod  ) ;
 UPDATE se_ART_COMPMESURE tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 DELETE FROM se_ART_COMPMESURE WHERE comp_num=0 ;

 CALL uvse_synchroverserp('comp_num@mesure_cod','ART_COMPMESURE','O') ;
 commit;

 
SELECT &outils.stragg(source) source,mesure_cod, comp_num, typunite_cod, enreg_id, compmesure_rang, enreg_s, formule_ok, compmesure_val, combinaison_rang
FROM (
  SELECT 'uv' source ,g.* FROM se_ART_COMPMESURE g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ART_COMPMESURE g, &erp.ART_REFE refe where refe.comp_num=g.comp_num
  )
GROUP BY mesure_cod, comp_num, typunite_cod, enreg_id, compmesure_rang, enreg_s, formule_ok, compmesure_val, combinaison_rang
HAVING Count(1)!=2 
ORDER BY comp_num,mesure_cod ;




 /*  d�phasage mesure entre complet, rgp et generique  */
 /*SELECT m.id,m.clergp,m.codgestion,m.clasvolume,m.libre3_tairef,m.libre4_lonref,g.id,g.codgestion,g.clasvolume,g.libre3_tairef,g.libre4_lonref

 FROM uv_pproduits m, uv_pproduits g
 WHERE m.codgestion in ('5','3')
   AND m.prodbase=g.id
   AND (
       (m.clasvolume||'' != g.clasvolume||'')
    or (m.libre3_tairef||'' != g.libre3_tairef||'' )
    or (m.libre4_lonref||'' != g.libre4_lonref||'' ))
 */